Snowflakeのストリームはどの操作によりレコードが消えるか確かめてみた #SnowflakeDB
※本エントリは、Snowflakeをより使いこなそう！ Advent Calendar 2021の1日目の記事となります。
さがらです。
Snowflakeのストリームに対して、どういった操作を行うとストリーム内のデータが削除されるのか、確かめてみました。
ストリームとは
ストリームはテーブルやビュー等と同じように扱えるオブジェクトの1種です。このストリームをテーブルに対して設定すると、そのテーブルの変更履歴をストリームに追跡・記録してくれます。「変更データキャプチャ(Change Data Capture)」という手法・仕組みを実現できるものとなっています。
Snowpipeやタスクと組み合わせることで、「新規または変更のあったレコードだけに処理を行って、その処理結果を分析用のテーブルにいれたい」「有効・無効フラグを付けてこれまでのレコード履歴を全て保持するテーブルを作りたい」等のようなことをしたい時に役立つ機能です。
弊社でも検証を行っておりますので、ぜひこちらも読んで頂けると良いと思います。
ストリームの特徴とこのブログで試すこと
ストリームは対象テーブルの変更を追うことができるのですが、ストリームを用いたDML(データ操作言語)を発行すると、ストリーム上のレコードが消えるという仕様があります。
ただ、DMLといっても、SELECT、INSERT、など様々なコマンドがあります。
そこで、ストリームに対してどういったDMLを実行するとストリーム上のレコードが消えるのか、確かめてみました。
その１：SELECT
まずは、定番のSELECT文から試してみます。
下記のクエリを実行して、テーブルとストリームを作ってみます。
CREATE DATABASE stream_test;
CREATE TABLE sample_tbl AS
SELECT
1 as no;
CREATE STREAM sample_stream ON TABLE sample_tbl;
INSERT INTO sample_tbl
SELECT
2 as no;
この状態で下記のようにSELECT文を2回ストリームに対して実行して、1回目のSELECT文でストリームの内容が消えていないか確かめてみます。
-- 1回目のSELECT SELECT * FROM sample_stream; -- 2回目のSELECT SELECT * FROM sample_stream;
結果：ストリームのデータは消えませんでした！
まあ、ストリームの内容を確かめたくて実行したSELECT文でストリームの内容が消えてしまったら、やっていられないですよね…笑
その２：ストリームに対してSELECT *した結果のINSERT
次は、ストリームに対するSELECT文を用いたINSERT文を実行してみます。
まずは前準備として下記のクエリを実行します。その１との違いは、ストリームに保持されている履歴データを保持するテーブルsample_tbl_historyも作成します。
CREATE OR REPLACE DATABASE stream_test;
CREATE OR REPLACE TABLE sample_tbl AS
SELECT
1 as no;
CREATE OR REPLACE STREAM sample_stream ON TABLE sample_tbl;
INSERT INTO sample_tbl
SELECT
2 as no;
CREATE OR REPLACE TABLE sample_tbl_history (
no number,
dml_type varchar(10)
);
この時点で、ストリームには1レコードだけ登録されています。
この状態で、ストリームに対するSELECT文を含むINSERT文を実行し、ストリームにデータが残っているかを確認してみます。
INSERT INTO sample_tbl_history
SELECT
no,
METADATA$ACTION
FROM
sample_stream;
SELECT * FROM sample_stream;
結果：ストリームのデータは全て消えました！
INSERTと併せて使うと、消えてしまうようです。
その３：ストリームに対してフィルタをかけた結果のINSERT
次は同じINSERTでも、ストリームに対してWHERE句でフィルタをかけた結果をINSERTすることを考えてみます。
前準備として下記のクエリを実行します。その２との違いは、2レコードINSERTしていることです。
CREATE OR REPLACE DATABASE stream_test;
CREATE OR REPLACE TABLE sample_tbl AS
SELECT
1 as no;
CREATE OR REPLACE STREAM sample_stream ON TABLE sample_tbl;
INSERT INTO sample_tbl
SELECT
3 as no;
INSERT INTO sample_tbl
SELECT
4 as no;
この時点で、ストリームには2レコード登録されています。
この状態で、WHERE句を含むストリームに対するSELECT文を含むINSERT文を実行し、ストリームにデータが残っているかを確認してみます。
INSERT INTO sample_tbl_history
SELECT
no,
METADATA$ACTION
FROM
sample_stream
WHERE
no = 3;
結果：ストリームのデータは全て消えました！
なんと、WHERE句で絞られて出力されるレコードだけではなく、全てのレコードがストリームから消えてしまいました。
その４：ストリームを副問合せとして用いたDELETE
次は、ストリームを副問合せとして用いたDELETE文を実行してみます。
前準備として下記のクエリを実行します。sample_tbl_2という、「3」だけ登録したDELETE文実行用のテーブルも用意していることがポイントです。
CREATE OR REPLACE DATABASE stream_test;
CREATE OR REPLACE TABLE sample_tbl AS
SELECT
1 as no;
CREATE OR REPLACE STREAM sample_stream ON TABLE sample_tbl;
INSERT INTO sample_tbl
SELECT
3 as no;
INSERT INTO sample_tbl
SELECT
4 as no;
CREATE OR REPLACE TABLE sample_tbl_2 AS
SELECT
3 as no;
この時点で、ストリームには2レコード登録されています。
この状態で、下記のようにストリームを副問合せとして用いたDELETE文を実行してみます。
DELETE FROM sample_tbl_2 tbl2 WHERE EXISTS (SELECT 'aaa' FROM sample_stream str WHERE tbl2.no = str.no);
結果：ストリームのデータは全て消えました！
その３と同様に、ストリームにおいて関連のあるレコードは一部だけであっても、全てのレコードがストリームから消えてしまいました。
まとめ
ということで、とても簡単な検証でしたがまとめとなります。
- ストリームをSELECTするだけでは、ストリームのデータは消えない。
- ストリームを用いたINSERT、DELETEを用いると、対象のストリームの一部のレコードだけを参照していても、全てのストリームのレコードが消える
ストリームを用いたINSERTやDELETEを行う際は、WHERE句などにより絞り込まれたレコードだけが消えるわけではなく、ストリーム上の全てのレコード消えてしまうので注意しましょう！
次回
Snowflakeをより使いこなそう！ Advent Calendar 2021、次回の2日目では、「Snowflakeのタイムトラベル関連でよく使いそうなコマンドを試してみた」というタイトルで執筆します。お楽しみに！
